﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class RmsFileDao
    {
        public bool ChangeRoll(SqlConnection conn, int fileId, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Rms_File] ");
            builder.Append("SET ");
            builder.Append("\t[RollId] = @RollId ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = fileId;
            commandParameters[1] = new SqlParameter("@RollId", SqlDbType.Int);
            commandParameters[1].Value = rollId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Delete(SqlConnection conn, int fileId, string delUser)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE\t[dbo].[Rms_File] ");
            builder.Append("SET ");
            builder.Append("\t[DelUser] = @DelUser, ");
            builder.Append("\t[DelTime] = GETDATE() ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = fileId;
            commandParameters[1] = new SqlParameter("@DelUser", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = delUser;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Destroy(SqlConnection conn, int fileId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FileId", SqlDbType.Int) };
            commandParameters[0].Value = fileId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int GetLendNums(SqlConnection conn, int fileId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\tCount([LendId]) AS Nums ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend] ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[30];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = fileId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public int Insert(SqlConnection conn, RmsFileData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Rms_File] ");
            builder.Append("( ");
            builder.Append("\t[RollId], ");
            builder.Append("\t[FileCode], ");
            builder.Append("\t[FileSubject], ");
            builder.Append("\t[FileTitle], ");
            builder.Append("\t[FileTitle0], ");
            builder.Append("\t[SendUnit], ");
            builder.Append("\t[SendDate], ");
            builder.Append("\t[Secret], ");
            builder.Append("\t[Urgency], ");
            builder.Append("\t[FileKind], ");
            builder.Append("\t[FileType], ");
            builder.Append("\t[FilePage], ");
            builder.Append("\t[PrintPage], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[AddUser], ");
            builder.Append("\t[AddTime], ");
            builder.Append("\t[ModUser], ");
            builder.Append("\t[ModTime], ");
            builder.Append("\t[DelUser], ");
            builder.Append("\t[DelTime], ");
            builder.Append("\t[Borrow], ");
            builder.Append("\t[BorrowStatus], ");
            builder.Append("\t[ConfirmUser], ");
            builder.Append("\t[ConfirmStatus] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@RollId, ");
            builder.Append("\t@FileCode, ");
            builder.Append("\t@FileSubject, ");
            builder.Append("\t@FileTitle, ");
            builder.Append("\t@FileTitle0, ");
            builder.Append("\t@SendUnit, ");
            builder.Append("\t@SendDate, ");
            builder.Append("\t@Secret, ");
            builder.Append("\t@Urgency, ");
            builder.Append("\t@FileKind, ");
            builder.Append("\t@FileType, ");
            builder.Append("\t@FilePage, ");
            builder.Append("\t@PrintPage, ");
            builder.Append("\t@Remark, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@AddUser, ");
            builder.Append("\t@AddTime, ");
            builder.Append("\t@ModUser, ");
            builder.Append("\t@ModTime, ");
            builder.Append("\t@DelUser, ");
            builder.Append("\t@DelTime, ");
            builder.Append("\t@Borrow, ");
            builder.Append("\t@BorrowStatus, ");
            builder.Append("\t@ConfirmUser, ");
            builder.Append("\t@ConfirmStatus ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[0x19];
            commandParameters[0] = new SqlParameter("@RollId", SqlDbType.Int);
            if (data.RollIdIsNull)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = data.RollId;
            }
            commandParameters[1] = new SqlParameter("@FileCode", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.FileCode;
            commandParameters[2] = new SqlParameter("@FileSubject", SqlDbType.NVarChar, 200);
            if (data.FileSubjectIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.FileSubject;
            }
            commandParameters[3] = new SqlParameter("@FileTitle", SqlDbType.NVarChar, 200);
            commandParameters[3].Value = data.FileTitle;
            commandParameters[4] = new SqlParameter("@FileTitle0", SqlDbType.NVarChar, 200);
            if (data.FileTitle0IsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.FileTitle0;
            }
            commandParameters[5] = new SqlParameter("@SendUnit", SqlDbType.NVarChar, 200);
            if (data.SendUnitIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.SendUnit;
            }
            commandParameters[6] = new SqlParameter("@SendDate", SqlDbType.DateTime);
            if (data.SendDateIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.SendDate;
            }
            commandParameters[7] = new SqlParameter("@Secret", SqlDbType.NVarChar, 200);
            if (data.SecretIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Secret;
            }
            commandParameters[8] = new SqlParameter("@Urgency", SqlDbType.NVarChar, 200);
            if (data.UrgencyIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Urgency;
            }
            commandParameters[9] = new SqlParameter("@FileKind", SqlDbType.NVarChar, 200);
            if (data.FileKindIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.FileKind;
            }
            commandParameters[10] = new SqlParameter("@FileType", SqlDbType.NVarChar, 200);
            if (data.FileTypeIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.FileType;
            }
            commandParameters[11] = new SqlParameter("@FilePage", SqlDbType.NVarChar, 20);
            if (data.FilePageIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.FilePage;
            }
            commandParameters[12] = new SqlParameter("@PrintPage", SqlDbType.NVarChar, 20);
            if (data.PrintPageIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.PrintPage;
            }
            commandParameters[13] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.Remark;
            }
            commandParameters[14] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.AttachmentId;
            }
            commandParameters[15] = new SqlParameter("@AddUser", SqlDbType.NVarChar, 20);
            commandParameters[15].Value = data.AddUser;
            commandParameters[0x10] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[0x10].Value = data.AddTime;
            commandParameters[0x11] = new SqlParameter("@ModUser", SqlDbType.NVarChar, 20);
            if (data.ModUserIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.ModUser;
            }
            commandParameters[0x12] = new SqlParameter("@ModTime", SqlDbType.DateTime);
            if (data.ModTimeIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.ModTime;
            }
            commandParameters[0x13] = new SqlParameter("@DelUser", SqlDbType.NVarChar, 20);
            if (data.DelUserIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.DelUser;
            }
            commandParameters[20] = new SqlParameter("@DelTime", SqlDbType.DateTime);
            if (data.DelTimeIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.DelTime;
            }
            commandParameters[0x15] = new SqlParameter("@Borrow", SqlDbType.Int);
            commandParameters[0x15].Value = data.Borrow;
            commandParameters[0x16] = new SqlParameter("@BorrowStatus", SqlDbType.Int);
            commandParameters[0x16].Value = data.BorrowStatus;
            commandParameters[0x17] = new SqlParameter("@ConfirmUser", SqlDbType.NVarChar, 20);
            if (data.ConfirmUserIsNull)
            {
                commandParameters[0x17].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x17].Value = data.ConfirmUser;
            }
            commandParameters[0x18] = new SqlParameter("@ConfirmStatus", SqlDbType.Int);
            commandParameters[0x18].Value = data.ConfirmStatus;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool Recover(SqlConnection conn, int fileId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE\t[dbo].[Rms_File] ");
            builder.Append("SET ");
            builder.Append("\t[DelUser] = NULL, ");
            builder.Append("\t[DelTime] = NULL ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FileId", SqlDbType.Int) };
            commandParameters[0].Value = fileId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectByFileId(SqlConnection conn, int fileId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[FileId], ");
            builder.Append("\t[RollId], ");
            builder.Append("\t[FileCode], ");
            builder.Append("\t[FileSubject], ");
            builder.Append("\t[FileTitle], ");
            builder.Append("\t[FileTitle0], ");
            builder.Append("\t[SendUnit], ");
            builder.Append("\t[SendDate], ");
            builder.Append("\t[Secret], ");
            builder.Append("\t[Urgency], ");
            builder.Append("\t[FileKind], ");
            builder.Append("\t[FileType], ");
            builder.Append("\t[FilePage], ");
            builder.Append("\t[PrintPage], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[AddUser], ");
            builder.Append("\t[AddTime], ");
            builder.Append("\t[ModUser], ");
            builder.Append("\t[ModTime], ");
            builder.Append("\t[DelUser], ");
            builder.Append("\t[DelTime], ");
            builder.Append("\t[Borrow], ");
            builder.Append("\t[BorrowStatus], ");
            builder.Append("\t[ConfirmUser], ");
            builder.Append("\t[ConfirmStatus] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FileId", SqlDbType.Int) };
            commandParameters[0].Value = fileId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_File";
            return set.Tables[0];
        }

        public DataTable SelectDeletePaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([FileId]) FROM [dbo].[Rms_File]  WHERE [DelUser] IS NOT NULL ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [FileId] FROM [dbo].[Rms_File]  WHERE [DelUser] IS NOT NULL ORDER BY [FileId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Rms_File].[FileId], ");
            builder.Append("\t\t\t[Rms_File].[RollId], ");
            builder.Append("\t\t\t[Rms_File].[FileCode], ");
            builder.Append("\t\t\t[Rms_File].[FileTitle], ");
            builder.Append("\t\t\t[Rms_File].[SendUnit], ");
            builder.Append("\t\t\t[Rms_File].[SendDate], ");
            builder.Append("\t\t\t[Rms_File].[Secret], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Rms_File], ");
            builder.Append("\t\t\t@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE\t\t\t ");
            builder.Append("\t\t\t[Rms_File].[FileId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Rms_File].[FileId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_File";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([FileId]) FROM [dbo].[Rms_File]  WHERE [DelUser] IS NULL AND ([RollId] IS NULL OR [RollId] IN(SELECT [RollId] FROM [dbo].[Rms_Roll] WHERE [Status] = 0))  ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [FileId] FROM [dbo].[Rms_File]  WHERE [DelUser] IS NULL AND ([RollId] IS NULL OR [RollId] IN(SELECT [RollId] FROM [dbo].[Rms_Roll] WHERE [Status] = 0)) ORDER BY [FileId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Rms_File].[FileId], ");
            builder.Append("\t\t\t[Rms_File].[RollId], ");
            builder.Append("\t\t\t[Rms_File].[FileCode], ");
            builder.Append("\t\t\t[Rms_File].[FileTitle], ");
            builder.Append("\t\t\t[Rms_File].[SendUnit], ");
            builder.Append("\t\t\t[Rms_File].[SendDate], ");
            builder.Append("\t\t\t[Rms_File].[Secret], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Rms_File], ");
            builder.Append("\t\t\t@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE\t\t\t ");
            builder.Append("\t\t\t[Rms_File].[FileId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Rms_File].[FileId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_File";
            return set.Tables[0];
        }

        public DataTable SelectPagedByRollId(SqlConnection conn, int pagesize, int pageindex, bool docount, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([FileId]) FROM [dbo].[Rms_File]  WHERE [DelUser] IS NULL AND [RollId] = @RollId ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [FileId] FROM [dbo].[Rms_File]  WHERE [DelUser] IS NULL AND [RollId] = @RollId ORDER BY [FileId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Rms_File].[FileId], ");
            builder.Append("\t\t\t[Rms_File].[RollId], ");
            builder.Append("\t\t\t[Rms_File].[FileCode], ");
            builder.Append("\t\t\t[Rms_File].[FileTitle], ");
            builder.Append("\t\t\t[Rms_File].[SendUnit], ");
            builder.Append("\t\t\t[Rms_File].[SendDate], ");
            builder.Append("\t\t\t[Rms_File].[Secret], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Rms_File], ");
            builder.Append("\t\t\t@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE\t\t\t ");
            builder.Append("\t\t\t[Rms_File].[FileId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Rms_File].[FileId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@RollId", SqlDbType.Int);
            commandParameters[3].Value = rollId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_File";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, RmsFileData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Rms_File] SET ");
            builder.Append("\t[RollId] = @RollId, ");
            builder.Append("\t[FileCode] = @FileCode, ");
            builder.Append("\t[FileSubject] = @FileSubject, ");
            builder.Append("\t[FileTitle] = @FileTitle, ");
            builder.Append("\t[FileTitle0] = @FileTitle0, ");
            builder.Append("\t[SendUnit] = @SendUnit, ");
            builder.Append("\t[SendDate] = @SendDate, ");
            builder.Append("\t[Secret] = @Secret, ");
            builder.Append("\t[Urgency] = @Urgency, ");
            builder.Append("\t[FileKind] = @FileKind, ");
            builder.Append("\t[FileType] = @FileType, ");
            builder.Append("\t[FilePage] = @FilePage, ");
            builder.Append("\t[PrintPage] = @PrintPage, ");
            builder.Append("\t[Remark] = @Remark, ");
            builder.Append("\t[AttachmentId] = @AttachmentId, ");
            builder.Append("\t[ModUser] = @ModUser, ");
            builder.Append("\t[ModTime] = @ModTime ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] = @FileId ");
            SqlParameter[] commandParameters = new SqlParameter[0x1a];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = data.FileId;
            commandParameters[1] = new SqlParameter("@RollId", SqlDbType.Int);
            if (data.RollIdIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.RollId;
            }
            commandParameters[2] = new SqlParameter("@FileCode", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.FileCode;
            commandParameters[3] = new SqlParameter("@FileSubject", SqlDbType.NVarChar, 200);
            if (data.FileSubjectIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.FileSubject;
            }
            commandParameters[4] = new SqlParameter("@FileTitle", SqlDbType.NVarChar, 200);
            commandParameters[4].Value = data.FileTitle;
            commandParameters[5] = new SqlParameter("@FileTitle0", SqlDbType.NVarChar, 200);
            if (data.FileTitle0IsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.FileTitle0;
            }
            commandParameters[6] = new SqlParameter("@SendUnit", SqlDbType.NVarChar, 200);
            if (data.SendUnitIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.SendUnit;
            }
            commandParameters[7] = new SqlParameter("@SendDate", SqlDbType.DateTime);
            if (data.SendDateIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.SendDate;
            }
            commandParameters[8] = new SqlParameter("@Secret", SqlDbType.NVarChar, 200);
            if (data.SecretIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Secret;
            }
            commandParameters[9] = new SqlParameter("@Urgency", SqlDbType.NVarChar, 200);
            if (data.UrgencyIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Urgency;
            }
            commandParameters[10] = new SqlParameter("@FileKind", SqlDbType.NVarChar, 200);
            if (data.FileKindIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.FileKind;
            }
            commandParameters[11] = new SqlParameter("@FileType", SqlDbType.NVarChar, 200);
            if (data.FileTypeIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.FileType;
            }
            commandParameters[12] = new SqlParameter("@FilePage", SqlDbType.NVarChar, 20);
            if (data.FilePageIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.FilePage;
            }
            commandParameters[13] = new SqlParameter("@PrintPage", SqlDbType.NVarChar, 20);
            if (data.PrintPageIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.PrintPage;
            }
            commandParameters[14] = new SqlParameter("@Remark", SqlDbType.NText);
            if (data.RemarkIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.Remark;
            }
            commandParameters[15] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.AttachmentId;
            }
            commandParameters[0x10] = new SqlParameter("@AddUser", SqlDbType.NVarChar, 20);
            commandParameters[0x10].Value = data.AddUser;
            commandParameters[0x11] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[0x11].Value = data.AddTime;
            commandParameters[0x12] = new SqlParameter("@ModUser", SqlDbType.NVarChar, 20);
            if (data.ModUserIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.ModUser;
            }
            commandParameters[0x13] = new SqlParameter("@ModTime", SqlDbType.DateTime);
            if (data.ModTimeIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.ModTime;
            }
            commandParameters[20] = new SqlParameter("@DelUser", SqlDbType.NVarChar, 20);
            if (data.DelUserIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.DelUser;
            }
            commandParameters[0x15] = new SqlParameter("@DelTime", SqlDbType.DateTime);
            if (data.DelTimeIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.DelTime;
            }
            commandParameters[0x16] = new SqlParameter("@Borrow", SqlDbType.Int);
            commandParameters[0x16].Value = data.Borrow;
            commandParameters[0x17] = new SqlParameter("@BorrowStatus", SqlDbType.Int);
            commandParameters[0x17].Value = data.BorrowStatus;
            commandParameters[0x18] = new SqlParameter("@ConfirmUser", SqlDbType.NVarChar, 20);
            if (data.ConfirmUserIsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.ConfirmUser;
            }
            commandParameters[0x19] = new SqlParameter("@ConfirmStatus", SqlDbType.Int);
            commandParameters[0x19].Value = data.ConfirmStatus;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

